package com.yenlien.traceyun.control.dao;

import com.yenlien.traceyun.control.entity.LoanInfo;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 贷款信息
 */
public interface LoanInfoDao extends BaseJpaDao<LoanInfo> {

    @Query(value = "select lf.id,lf.id_card,lf.amount,lf.bank_info_id,lf.business_license_num," +
            "lf.create_time,lf.guarantee_company_name," +
            "lf.guarantee_company_legal_person,lf.guarantee_company_legal_person_id_card," +
            "lf.contacts_people,lf.contacts_tel,lf.mortgage,lf.mortgage_value,lf.legal_person," +
            "lf.area,lf.position,lf.species,lf.term,lf.purpose,lf.usufruct,lf.land_num," +
            "lf.apply_img,lf.business_img,lf.id_card_just_img,lf.id_card_back_img,lf.natural_img,lf.prove_img,lf.shares_img," +
            "lf.loan_period,lf.rate,lf.monthly_amount,lf.name,lf.remarks,lf.status,lf.company_id,bf.name as branchBankName,bf.pid," +
            "bl.name as bankName,bl.img_path from t_loan_info lf" +
            " inner join t_bank_info bf on bf.id=lf.bank_info_id" +
            " inner join t_bank_level bl on bl.id=bf.pid" +
            " where lf.id=?1", nativeQuery = true)
    List<Map<String, Object>> findById(String id);


    @Transactional
    @Modifying
    @Query(value = "update t_loan_info set status=?2,update_time=?3 where id=?1", nativeQuery = true)
    Integer updateStatus(String id, String status, Date updateTime);


    @Transactional
    @Modifying
    @Query(value = "update t_loan_info set status=?2,update_time=?3,rate=?4,monthly_amount=?5 where id=?1", nativeQuery = true)
    Integer updateStatus2(String id, String status, Date updateTime, String rate, BigDecimal amount);


    @Query(value = "select lf.id,lf.amount,lf.area,lf.bank_info_id," +
            "lf.business_license_num,lf.contacts_people,lf.contacts_tel," +
            "lf.create_time,lf.guarantee_company_legal_person," +
            "lf.guarantee_company_legal_person_id_card,lf.guarantee_company_name," +
            "lf.id_card,lf.land_num,lf.legal_person,lf.mortgage,lf.mortgage_value," +
            "lf.name,lf.position,lf.purpose,lf.remarks,lf.species,lf.`status`,lf.term," +
            "lf.usufruct,lf.company_id,lf.update_time,lf.loan_period," +
            "lf.apply_img,lf.business_img,lf.id_card_just_img,lf.id_card_back_img,lf.natural_img,lf.prove_img,lf.shares_img," +
            "b.name as bankName,(select sum(amount) from t_repayment where loan_id=?1 ) as totalAmount " +
            " from t_loan_info lf inner join t_repayment p on p.loan_id=lf.id" +
            " inner join t_bank_info b on b.id=lf.bank_info_id where lf.id=?1", nativeQuery = true)
    List<Map<String, Object>> findByIdCustomer(String id);

    @Query(value = "select lf.id,lf.id_card,lf.amount,lf.bank_info_id,lf.business_license_num," +
            "lf.create_time,lf.guarantee_company_name," +
            "lf.guarantee_company_legal_person,lf.guarantee_company_legal_person_id_card," +
            "lf.contacts_people,lf.contacts_tel,lf.mortgage,lf.mortgage_value,lf.legal_person," +
            "lf.area,lf.position,lf.species,lf.term,lf.purpose,lf.usufruct,lf.land_num," +
            "lf.apply_img,lf.business_img,lf.id_card_just_img,lf.id_card_back_img,lf.natural_img,lf.prove_img,lf.shares_img," +
            "lf.loan_period,lf.name,lf.remarks,lf.status,lf.company_id,b.name as bankName" +
            " from t_loan_info lf inner join t_bank_info b on b.id=lf.bank_info_id" +
            " where lf.id=?1", nativeQuery = true)
    List<Map<String, Object>> findById2(String id);
}
